Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Sql

Imports System.Data.Common

Namespace Seuss.EnterpriseLibrary.DataAccess

    Public Class DAL_Kiosco

#Region "Atributos"
        Dim Db As Database
#End Region

#Region "Constructores"
        Public Sub New()
            Db = DatabaseFactory.CreateDatabase
        End Sub

        Public Sub New(ByVal NewDb As Database)          
            Db = NewDb
        End Sub
#End Region

#Region "Propiedades"

        ReadOnly Property Database() As Database
            Get
                Return Db
            End Get
        End Property

#End Region

#Region "Metodos: Acceso a Datos"

#Region "Select-Get"

        Public Function Get_cPerJuridicaDocente_by_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_cPerJuridica_Docente_by_cPerCodigo")
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Filial_by_cPerCodigoAdm(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_Filial_by_cPerCodigo")
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Notas_By_Registro_Matricula_Descripcion(ByVal Registro As String, ByVal matricula As String, ByVal periodo As String, ByVal RegistroBase As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_Notas_Descripcion_By_Registro_Matricula_cSubPrdCodigo_RegistroBase")
                Db.AddInParameter(Cmd, "Registro", System.Data.DbType.String, Registro)
                Db.AddInParameter(Cmd, "matricula", System.Data.DbType.String, matricula)
                Db.AddInParameter(Cmd, "cSubPrdCodigo", System.Data.DbType.String, periodo)
                Db.AddInParameter(Cmd, "RegistroBase", System.Data.DbType.String, RegistroBase)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_nCurGruCodigoBase_By_nCurgruCodigo(ByVal nCurGruCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_nCurGruCodigoBase_By_nCurgruCodigo")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_PromedioParcial_By_cDocCodigo_nCurGruCodigo_nSubPrdCodigo(ByVal Matricula As String, ByVal nCurGruCodigo As Long, ByVal Unidad As Integer) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_PromedioParcial_By_cDocCodigo_nCurGruCodigo_nSubPrdCodigo")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, Matricula)
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Db.AddInParameter(Cmd, "nSubPrdCodigo", System.Data.DbType.Int32, Unidad)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function



        Public Function Get_cDocCodigoMatricula_By_cPerCodigo_nCurGruCodigo(ByVal cPerCodigo As String, ByVal nCurGruCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_cDocCodigoMatricula_By_cPerCodigo_nCurGruCodigo")
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function
        Public Function Get_Registro_By_nCurGruCodigo(ByVal nCurGruCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_Registro_By_nCurGruCodigo")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Parciales_By_cDocCodigo(ByVal cDocCodigo As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_Parciales_By_cDocCodigo")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_cPerCodigo_By_Usuario(ByVal Usuario As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_Get_cPerCodigo_By_Usuario")
                Db.AddInParameter(Cmd, "@Usuario", System.Data.DbType.String, Usuario)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_cPerCodigo_And_NombreCompleto_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As String
                vsql = "select cpercodigo,(cperapellido+' '+ cpernombre) as NombreCompleto from persona where cpercodigo='" + cPerCodigo + "'"
                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_PeriodoAcademico_Actual() As DbCommand
            Try
                Dim vsql As String
                vsql = "select top 1 nprdcodigo from periodo where nPrdActividad=2 and nPrdEstado=1"
                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_PeriodoAdmision_Actual(ByVal cPerCodigo As String, ByVal nCurCodigo As Integer) As DbCommand
            Try
                Dim vsql As String
                vsql = "select top 1 nprdadmcodigo from perAluRegistra where cPerCodigo='" + cPerCodigo.ToString + "' and ncurcodigo=" + nCurCodigo.ToString + ""
                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Perfiles_By_cPerCodigo(ByVal cPerCodigo As String, ByVal cPerJuridica As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" Declare @cPerCodigo varchar(20) Declare @Alumno int Declare @Docente int Declare @Administrativo int ")
                vsql.Append(" Declare @Padre int set @cPerCodigo='" + cPerCodigo.ToString + "' ")
                vsql.Append(" set @alumno=(select count(*) from peraluregistra where cPerCodigo=@cPerCodigo and nPerAluRegEstado=1) ")
                vsql.Append(" set @docente=(select count(distinct cg.nCurGruCodigo) from curdetpersonas cdp ")
                vsql.Append(" inner join grupos gru on gru.nGruCodigo=cdp.ngruCodigo inner join curgrupo cg on cg.nCurGruCodigo=gru.nCurGruCodigo ")
                vsql.Append(" inner join persona per on per.cPerCodigo=cdp.cPerCodigo where cg.nprdCodigo in (select nPrdCodigo from periodo where nPrdActividad IN (2,10)) ")
                vsql.Append(" and per.cpercodigo=@cPerCodigo) ")
                vsql.Append(" Set @Administrativo=(SELECT count(PD.cPerCodigo) ")
                vsql.Append(" FROM dbo.PerDevengo AS PD with (nolock)  INNER JOIN dbo.Devengo AS D with (nolock) ON PD.nDgoCodigo = D.nDgoCodigo  ")
                vsql.Append(" INNER JOIN dbo.Planilla with (nolock) ON D.nPllaCodigo = dbo.Planilla.nPllaCodigo inner join (SELECT cPerCodigo as cPerJurcodigo, nObjCodigo as nPrdCodigo  ")
                vsql.Append(" FROM Perusugruacc with (nolock) where nsisgrucodigo = 1007 and nsisgrutipo = 4003 and nobjtipo = 1007 /* AND  cPerCodigo='" + cPerJuridica.ToString + " ' */) PJ  on PJ.nPrdCodigo= D.nPrdCodigo ")
                vsql.Append(" WHERE  Planilla.nPllaCodigo IN (2, 3, 4) AND (PD.cParCodigo = 'PMES_TREM') AND (PD.fValor IN(1,0)) And cPerCodigo=@cPerCodigo) ")
                vsql.Append(" set @Padre=(SELECT Count (*) FROM  PerParentesco PP    INNER JOIN Persona P    ON PP.cPerParCodigo = P.cPerCodigo  ")
                vsql.Append(" inner join constante C    on PP.nPerParTipo=C.nConvalor WHERE nConCodigo=1006 and PP.cPerParCodigo =@cPerCodigo) ")
                vsql.Append(" select case when @alumno=0 then 'False' else 'True' end as IsAlumno,case when @Docente=0 then 'False' else 'True' end as IsDocente, ")
                vsql.Append(" case when @Administrativo=0 then 'False' else 'True' end as IsAdministrativo,case when @Padre=0 then 'False' else 'True' end as IsPadre ")

                'Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_Get_Perfiles_By_cPerCodigo")
                'Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_cPerIdeNumero(ByVal cPerJuridica As String, ByVal nPerIdeTipo As String) As DbCommand
            Try
                Using cmd As DbCommand = Db.GetStoredProcCommand("Usp_PerIdentifica_Get_cPerIdeNumero_By_cPerCodigo_nPerIdeTipo")
                    Db.AddInParameter(cmd, "cPerJuridica", System.Data.DbType.String, cPerJuridica)
                    Db.AddInParameter(cmd, "nPerIdeTipo", System.Data.DbType.String, nPerIdeTipo)
                    Return cmd
                End Using
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Familiares_By_Padre(ByVal cPerCodigo As String, Optional ByVal cPerHijo As String = Nothing) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_Familiares_By_Padre")
                Db.AddInParameter(Cmd, "cPerPadreCodigo", System.Data.DbType.String, cPerCodigo)
                If cPerCodigo <> "" Then Db.AddInParameter(Cmd, "cPerHijoCodigo", System.Data.DbType.String, cPerHijo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_CtasCte_By_cPerCodigo(ByVal CodUniv As String, ByVal cPrdDescripcion As String, ByVal BDPremium As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" Declare @CodUniv AS VARCHAR(10)=NULL Declare @periodo AS VARCHAR(6)=NULL  ")
                vsql.Append(" set @CodUniv='" + CodUniv + "' set @periodo='" + cPrdDescripcion + "'  ")
                vsql.Append(" SELECT detalle.periodo,left(servicio.descserv,30) as descserv,(case when detalle.estadod='' then 'P' else detalle.estadod end) as estadod,Sum(detalle.impcuota) AS monto,  ")
                vsql.Append(" detalle.nrocuota,convert(nchar(10),Max(detalle.fechudp),103)  udp,Max(detalle.fechpago) pago,Max(detalle.fechregd) registro  ")
                vsql.Append(" FROM  " + BDPremium + ".dbo.detalle WITH(nolock)  ")
                vsql.Append(" INNER JOIN " + BDPremium + ".dbo.servicio WITH(nolock) ON detalle.servicio = servicio.servicio  ")
                vsql.Append(" WHERE detalle.alumno=@CodUniv AND detalle.estadod IN ( 'c', '' ) AND periodo = @periodo  ")
                vsql.Append(" GROUP  BY detalle.servicio,detalle.periodo,detalle.alumno,detalle.estadod,servicio.descserv,detalle.nrocuota  ")
                vsql.Append(" ORDER  BY detalle.nrocuota  ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_TasaEducativas_By_cPerJurCodigo(ByVal BDPremium As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" select DescServ,MontoS from " + BDPremium + ".dbo.Servicio where ")
                vsql.Append(" (DescServ ='MATRICULA' OR  DescServ ='EXAMEN DE ADMISION' OR DescServ ='CARNET DE BIBLIOTECA' OR DescServ ='CARNET UNIVERSITARIO' OR DescServ ='CARPETA DE TITULACION' OR ")
                vsql.Append(" DescServ ='CONST. DE ESTUDIOS' OR DescServ ='CONST. ORDEN MERITO' OR DescServ ='CONSTANCIA' OR DescServ ='DERECHO DE TRAMITE' OR ")
                vsql.Append(" DescServ ='DUPLICADO CARNE BIBLIOTECA' OR DescServ ='DUPLICADO CARNE UNIVERSITARIO' OR DescServ ='DUPLICADO DE RECIBO' OR DescServ ='EXAMEN DE  ADMISION' OR ")
                vsql.Append(" DescServ ='EXAMEN DE RECUPERACION' OR DescServ ='EXAMEN DE REZAGADO' OR DescServ ='EXAMEN DE SUFICIENCIA' OR DescServ ='FICHA SOCIOECONOMICA' OR ")
                vsql.Append(" DescServ ='INSCRIPCION' OR DescServ ='INSCRIPCION CEPRE UCV' OR DescServ ='SOLICITUD  DE CATEGORIA' OR DescServ ='TRASLADO INTERNO') order by DescServ ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_DeudaProgramadayMorosa_By_cPerCodigo(ByVal CodUniv As String, ByVal BDPremium As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" declare @DeudaProg float declare @DeudaMor Float declare @CodUniv nchar(10)  ")
                vsql.Append(" set @CodUniv='" + CodUniv + "'  ")
                vsql.Append(" set @DeudaProg=(Select sum(impcuota) Importe from " + BDPremium + ".dbo.detalle D  ")
                vsql.Append(" where alumno=@CodUniv ")
                vsql.Append(" and estadod= ' '  and convert(char(10),fechudp,102) >= convert(char(10),getdate(),102))  ")
                vsql.Append(" set @DeudaMor=(Select sum(impcuota) Importe from " + BDPremium + ".dbo.detalle D  ")
                vsql.Append(" where alumno=@CodUniv ")
                vsql.Append(" and estadod= ' '  and convert(char(10),fechudp,102) < convert(char(10),getdate(),102))  ")
                vsql.Append(" select isnull(@DeudaProg,0) as programada,isnull(@DeudaMor,0) as morosa  ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function
        Public Function Get_Curriculas_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" Declare @cPerCodigo nchar(10) set @cPerCodigo='" + cPerCodigo + "' ")
                vsql.Append(" SELECT  PAR.nCurCodigo,rtrim(Cur.cCurDescripcion)+' - '+ rtrim(UniOrg.cUniOrgNombre) ")
                vsql.Append(" as Descripcion,nprdAdmCodigo ")
                vsql.Append(" FROM     PerAluRegistra PAR with (nolock) ")
                vsql.Append(" INNER JOIN Curriculo Cur with (nolock) ON PAR.nCurCodigo = Cur.nCurCodigo ")
                vsql.Append(" INNER JOIN (SELECT I.nIntCodigo AS nUniOrgCodigo,I.cIntJerarquia AS cUniOrgCodigo,I.cIntNombre AS cUniOrgAbrev ,I.cIntDescripcion AS cUniOrgNombre, ")
                vsql.Append(" PUGA.cPerCodigo AS cPerJuridica ,I.nIntTipo,P.cPerApellido,P.cPerNombre, ")
                vsql.Append(" I.cIntJerarquia AS cUniOrgRelacion FROM   Interface I with (nolock) ")
                vsql.Append(" INNER JOIN PerUsuGruAcc PUGA with (nolock) ON I.nIntCodigo = PUGA.nObjCodigo AND I.nIntClase = PUGA.nObjTipo ")
                vsql.Append(" INNER JOIN Persona P with (nolock) ON PUGA.cPerCodigo = P.cPerCodigo ")
                vsql.Append(" WHERE P.nPerTipo = 2 AND I.nIntClase = 1006 AND PUGA.nSisGruCodigo = 1 AND PUGA.nSisGruTipo = 1004) UniOrg ON Cur.nUniOrgCodigo = UniOrg.nUniOrgCodigo ")
                vsql.Append(" INNER JOIN Persona PerJur with (nolock) ON UniOrg.cPerJuridica = PerJur.cPerCodigo ")
                vsql.Append(" INNER JOIN (SELECT nConValor,cConDescripcion FROM Constante C with (nolock) WHERE  nConCodigo = 1053) Tipo ON Cur.nTipCur = Tipo.nConValor ")
                vsql.Append(" LEFT OUTER JOIN PerPrograma AS PerProg with (nolock) ON PAR.cPerCodigo= PerProg.cPerCodigo AND PAR.nPrdAdmCodigo = PerProg.nPrdCodigo ")
                vsql.Append(" LEFT OUTER JOIN Requisito Req with (nolock) ")
                vsql.Append(" INNER JOIN PerAdmision PerAdm with (nolock) ON Req.cReqCodigo=PerAdm.nPerAdmTipo ")
                vsql.Append(" ON PAR.cPerCodigo= PerAdm.cPerCodigo AND PAR.nPrdAdmCodigo = PerAdm.nPrdCodigo ")
                vsql.Append(" LEFT OUTER JOIN (SELECT nConValor,cConDescripcion FROM Constante C with (nolock) WHERE  nConCodigo = 1122) Men ")
                vsql.Append(" INNER JOIN PerAluRegArea with (nolock) ON Men.nConValor  = PerAluRegArea.nMenCodigo ")
                vsql.Append(" ON PAR.cPerCodigo = PerAluRegArea.cPerCodigo AND PAR.nCurCodigo = PerAluRegArea.nCurCodigo ")
                vsql.Append(" LEFT OUTER JOIN (SELECT nConValor,cConDescripcion FROM Constante C with (nolock) WHERE  nConCodigo = 1044) Estado ON PAR.nPerAluRegEstado = Estado.nConValor ")
                vsql.Append(" LEFT OUTER JOIN Periodo PrdEgr with (nolock) ON PAR.nPrdEgrCodigo = PrdEgr.nPrdCodigo ")
                vsql.Append(" LEFT OUTER JOIN Periodo PrdAdm with (nolock) ON PAR.nPrdAdmCodigo = PrdAdm.nPrdCodigo ")
                vsql.Append(" LEFT OUTER JOIN Periodo PrdAca with (nolock) ON PAR.nPrdCodigo = PrdAca.nPrdCodigo ")
                vsql.Append(" WHERE PAR.cPerCodigo= @cPerCodigo  ")
                vsql.Append(" Union  ")
                vsql.Append(" select distinct dpc.nCurCodigo ,rtrim(C.cCurDescripcion ) as Descripcion,0 from docpercursos dpc with (nolock) ")
                vsql.Append(" inner join docperiodo dp with (nolock) on dp.cDocCodigo=dpc.cDocCodigo ")
                vsql.Append(" inner join periodo peri with (nolock) on dp.nPrdini=peri.nPrdcodigo ")
                vsql.Append(" inner join documentos d with (nolock) on d.cDocCodigo=dpc.cDocCodigo ")
                vsql.Append(" inner join docpersona dpe with (nolock) on dpc.cdoccodigo=dpe.cdoccodigo ")
                vsql.Append(" inner join curriculo c with (nolock) on c.ncurcodigo=dpc.ncurcodigo ")
                vsql.Append(" inner join unidadorganizacional u with (nolock) on c.nuniorgcodigo=u.nuniorgcodigo ")
                vsql.Append(" where cpercodigo=@cPerCodigo and peri.nprdactividad=10 	AND NDOCPERTIPO=1011 ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                '                Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_Get_Curriculas_By_cPerCodigo")
                ' Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_RecordHistorico_By_cPerCodigo_nCurCodigo(ByVal cPerCodigo As String, ByVal nCurCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_Get_RecordHistorico_By_cPerCodigo_nCurCodigo")
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "@nCurCodigo", System.Data.DbType.Int32, nCurCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_HorarioActualCorto_By_cPerCodigo_nPrdCodigo(ByVal cPerCodigo As String, ByVal nPrdCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_RegAca_Get_HorarioCorto_By_Alumno")
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "@nPrdCodigo", System.Data.DbType.Int32, nPrdCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Marcaciones_By_cPerCodigo(ByVal cPerJuridica As String, ByVal dFechaIni As String, ByVal dFechaFin As String, ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" declare	@cPerCodigo char(10) declare @dfechaIni char(8) declare	@dfechaFin char(8)DECLARE @contador int ")
                vsql.Append(" DECLARE @dFechaIniBuscar datetime DECLARE @dFechaFinBuscar datetime DECLARE @cNombres varchar(150) DECLARE @cPerIdeNumero varchar(30) ")
                vsql.Append(" DECLARE @dHoraActual datetime DECLARE @nHorCodigoActual int DECLARE @dHoraCompActual datetime DECLARE @nHorEstadoActual int ")
                vsql.Append(" DECLARE @cDocCodigoActual varchar(18) DECLARE @cDocObservActual varchar(255) DECLARE @nUniOrgCodigo int DECLARE @UniOrgNombre varchar(200) ")
                vsql.Append(" DECLARE @Observacion varchar(255) DECLARE @nFlag int = 1 DECLARE @cPerJurCodigo char(10)  ")
                vsql.Append(" DECLARE @MarcEntrada datetime,@HorarioEntrada datetime,@nHorEstadoEntrada int,@cDocCodigoEntrada varchar(18),@cDocObservEntrada varchar(255), ")
                vsql.Append(" @nEstadoEntrada int,@nEstadoActual int ")
                vsql.Append(" DECLARE @nTotalHoras numeric(9,2) DECLARE @nAmbienteExterno int SET NOCOUNT ON SET DATEFORMAT dmy ")
                vsql.Append(" set	@cPerCodigo='" + cPerCodigo.ToString + "' set @dfechaIni='" + dFechaIni.ToString + "' set	@dfechaFin='" + dFechaFin.ToString + "' SET @cPerJurCodigo='" + cPerJuridica.ToString + "' ")
                vsql.Append(" SET @dFechaIniBuscar=CONVERT(DATETIME,@dFechaIni, 101) SET @dFechaFinBuscar=CONVERT(DATETIME,@dFechaFin, 101) ")
                vsql.Append(" CREATE TABLE #ReportsIO ")
                vsql.Append(" (cPerCodigo char(10),Nombres varchar(150),cPerIdeNumero	varchar(25),nUniOrgCodigo int,UniOrgNombre	varchar(100),Fecha datetime, ")
                vsql.Append(" MarcIngreso datetime NULL,MarcSalida datetime NULL,	HorIngreso datetime NULL,HorSalida datetime NULL,Observacion varchar(255) NULL, ")
                vsql.Append(" nTotalHoras numeric(9,2) NULL,nFlag int NULL) ")
                vsql.Append(" SELECT P.Nombres,P.cPerIdeNumero,H.dHora,H.nHorCodigo,H.dHoraComp,H.nHorEstado,H.cDocObserv,LTRIM(H.cDocCodigo) as cDocCodigo, CONVERT(char(10),dHora,101) AS cFECHA, HO.nHorEstado as nEstado INTO #Marca ")
                vsql.Append(" FROM HoraIO H WITH(NOLOCK) ")
                vsql.Append(" INNER JOIN  (SELECT P.cPerCodigo,rtrim(cPerApellido) +' '+rtrim(cPerNombre) as Nombres,cPerIdeNumero FROM Persona P  WITH(NOLOCK) ")
                vsql.Append(" INNER JOIN PerIdentifica pid WITH(NOLOCK) ON	p.cPerCodigo=pid.cPerCodigo ")
                vsql.Append(" WHERE nPerIdeTipo IN (22,25,28) and p.cPerCodigo=@cPerCodigo) P ")
                vsql.Append(" ON H.cPerCodigo = P.cPerCodigo INNER JOIN Horario HO WITH(NOLOCK) ON H.nHorCodigo=HO.nHorCodigo AND HO.nHorEstado = 4 AND ISNULL(HO.nHorTipo,2)=2 ")
                vsql.Append(" WHERE H.cPerCodigo=@cPerCodigo AND dHora>=@dFechaIniBuscar AND dHora<@dFechaFinBuscar AND H.cPerJurCodigo = @cPerJurCodigo ")
                vsql.Append(" UNION ")
                vsql.Append(" SELECT Nombres, cPerIdeNumero, dHorInicio, H.nHorCodigo, dHorInicio, 2, '*', NULL, CONVERT(char(10),dHorInicio,101) AS cFECHA, H.nHorEstado as nEstado ")
                vsql.Append(" FROM Horario H WITH(NOLOCK) ")
                vsql.Append(" INNER JOIN DocInterface DI WITH(NOLOCK) ON H.nHorCodigo = DI.nIntCodigo AND DI.nIntClase = 2503 ")
                vsql.Append(" INNER JOIN DocPersona DP WITH(NOLOCK) ON DI.cDocCodigo = DP.cDocCodigo AND DP.nDocPerTipo = 1 ")
                vsql.Append(" INNER JOIN (SELECT P.cPerCodigo,rtrim(cPerApellido) +' '+rtrim(cPerNombre) as Nombres,cPerIdeNumero ")
                vsql.Append(" FROM Persona P  WITH(NOLOCK) ")
                vsql.Append(" INNER JOIN PerIdentifica pid WITH(NOLOCK) ON	p.cPerCodigo=pid.cPerCodigo WHERE nPerIdeTipo IN (22,25,28) and p.cPerCodigo=@cPerCodigo ")
                vsql.Append(" ) P ON DP.cPerCodigo = P.cPerCodigo ")
                vsql.Append(" INNER JOIN HorInterface HI WITH(NOLOCK) ON H.nHorCodigo = HI.nHorCodigo AND HI.nObjTipo = 1006 AND HI.nObjCodigo IN(SELECT nUniOrgCodigo FROM UnidadOrganizacional WHERE cPerJuridica = @cPerJurCodigo) ")
                vsql.Append(" WHERE nHorTipo = 2 AND nHorEstado IN(4,5) AND H.dHorInicio >= @dFechaIniBuscar AND H.dHorInicio < @dFechaFinBuscar ")
                vsql.Append(" AND NOT EXISTS(SELECT *  FROM HoraIO H2 WITH(NOLOCK) WHERE H2.nHorCodigo = H.nHorCodigo AND H2.dHoraComp = H.dHorInicio AND H2.cPerCodigo = DP.cPerCodigo) ")
                vsql.Append(" UNION ")
                vsql.Append(" SELECT Nombres, cPerIdeNumero, dHorFin, H.nHorCodigo, dHorFin, 2, '*', NULL, CONVERT(char(10),dHorInicio,101) AS cFECHA, H.nHorEstado as nEstado ")
                vsql.Append(" FROM Horario H WITH(NOLOCK) ")
                vsql.Append(" INNER JOIN DocInterface DI WITH(NOLOCK) ON H.nHorCodigo = DI.nIntCodigo AND DI.nIntClase = 2503 ")
                vsql.Append(" INNER JOIN DocPersona DP WITH(NOLOCK) ON DI.cDocCodigo = DP.cDocCodigo AND DP.nDocPerTipo = 1 ")
                vsql.Append(" INNER JOIN (SELECT P.cPerCodigo,rtrim(cPerApellido) +' '+rtrim(cPerNombre) as Nombres,cPerIdeNumero FROM Persona P  WITH(NOLOCK) ")
                vsql.Append(" INNER JOIN PerIdentifica pid WITH(NOLOCK) ON p.cPerCodigo=pid.cPerCodigo WHERE nPerIdeTipo IN (22,25,28) and p.cPerCodigo=@cPerCodigo) P ")
                vsql.Append(" ON DP.cPerCodigo = P.cPerCodigo ")
                vsql.Append(" INNER JOIN HorInterface HI WITH(NOLOCK) ON H.nHorCodigo = HI.nHorCodigo AND HI.nObjTipo = 1006 AND HI.nObjCodigo IN(SELECT nUniOrgCodigo FROM UnidadOrganizacional WHERE cPerJuridica = @cPerJurCodigo) ")
                vsql.Append(" WHERE nHorTipo = 2 AND nHorEstado IN(4,5) AND H.dHorInicio >= @dFechaIniBuscar AND H.dHorInicio < @dFechaFinBuscar ")
                vsql.Append(" AND NOT EXISTS(SELECT * FROM HoraIO H2 WITH(NOLOCK) WHERE H2.nHorCodigo = H.nHorCodigo AND H2.dHoraComp = H.dHorFin AND H2.cPerCodigo = DP.cPerCodigo) ")
                vsql.Append(" UPDATE A SET A.nHorEstado = 1,A.cDocCodigo = B.cDocCodigo,A.cDocObserv = B.cDocObserv,A.dHora = A.dHoraComp FROM #Marca A ")
                vsql.Append(" INNER JOIN #Marca B ON A.cPerIdeNumero = B.cPerIdeNumero AND A.nHorCodigo = B.nHorCodigo ")
                vsql.Append(" WHERE (B.cDocCodigo <> '' OR LEN(B.cDocObserv)>2)AND (ISNULL(A.cDocCodigo,'') = '') ")
                vsql.Append(" WHILE @dFechaIniBuscar < @dFechaFinBuscar ")
                vsql.Append(" BEGIN ")
                vsql.Append(" SET @contador = 1 ")
                vsql.Append(" DECLARE Marcaciones CURSOR FOR ")
                vsql.Append(" SELECT Nombres, cPerIdeNumero, dHora, nHorCodigo, dHoraComp, nHorEstado,cDocObserv, cDocCodigo, nEstado FROM #Marca ")
                vsql.Append(" WHERE CFECHA = CONVERT(CHAR(10),@dFechaIniBuscar,101) ORDER BY Nombres, dHoraComp ")
                vsql.Append(" OPEN Marcaciones ")
                vsql.Append(" FETCH NEXT FROM Marcaciones INTO @cNombres,@cPerIdeNumero,@dHoraActual,@nHorCodigoActual,@dHoraCompActual,@nHorEstadoActual, @cDocObservActual, @cDocCodigoActual, @nEstadoActual ")
                vsql.Append(" WHILE @@fetch_status=0 ")
                vsql.Append(" BEGIN ")
                vsql.Append(" IF @contador = 1 BEGIN ")
                vsql.Append(" SET @MarcEntrada = @dHoraActual SET @HorarioEntrada = @dHoraCompActual SET @nHorEstadoEntrada = @nHorEstadoActual ")
                vsql.Append(" SET @cDocObservEntrada = @cDocObservActual SET @nEstadoEntrada = @nEstadoActual SET @contador = 2 ")
                vsql.Append(" END ")
                vsql.Append(" ELSE BEGIN ")
                vsql.Append(" SELECT TOP 1 @UniOrgNombre=cUniOrgNombre, @nUniOrgCodigo = nUniOrgCodigo ")
                vsql.Append(" FROM HorInterface HI WITH(NOLOCK) INNER JOIN UnidadOrganizacional UO ON hi.nObjCodigo = nUniOrgCodigo ")
                vsql.Append(" WHERE HI.nHorCodigo=@nHorCodigoActual ")
                vsql.Append(" 					 ")
                vsql.Append(" 		SELECT @nAmbienteExterno = count(*)  FROM Horario H with(nolock) ")
                vsql.Append(" INNER JOIN HorInterface HI with(nolock) ON H.nHorCodigo = HI.nHorCodigo ")
                vsql.Append(" INNER JOIN	(SELECT BienRegistro.nbieregcodigo,cintdescripcion AS AMBIENTE,nIntCodigo as nCodAmbiente  ")
                vsql.Append(" FROM (SELECT nIntCodigo, cIntDescripcion  FROM Interface with(nolock) WHERE nIntClase = 6000) AS Bien ")
                vsql.Append(" INNER JOIN BienRegistro with(nolock) ON Bien.nIntCodigo = BienRegistro.nBieCodigo  ")
                vsql.Append(" ) Ambiente ")
                vsql.Append(" ON	Ambiente.nBieRegCodigo=HI.nObjCodigo AND HI.nObjTipo = 6006 ")
                vsql.Append(" INNER JOIN DocInterface Di WITH(NOLOCK) on Di.nIntCodigo=H.nHorCodigo ")
                vsql.Append(" INNER JOIN DocPersona Dp WITH(NOLOCK) on Dp.cDocCodigo=Di.cDocCodigo and nDocPerTipo=1 and ndoctipo=25 ")
                vsql.Append(" WHERE H.nHorEstado IN(4,5) AND nCodAmbiente=13983 AND h.dhorinicio <= @dHoraCompActual and @HorarioEntrada <= h.dHorFin AND DP.cPerCodigo = @cPerCodigo ")
                vsql.Append("  ")
                vsql.Append(" 		SET @Observacion = REPLACE(ISNULL(@cDocObservEntrada + (CASE WHEN @cDocObservActual<>@cDocObservEntrada THEN '-'+@cDocObservActual ELSE '' END),''),'*','') ")
                vsql.Append(" IF @nAmbienteExterno > 0 AND REPLACE(REPLACE(ISNULL(@Observacion,''),'*',''),'-','')='' ")
                vsql.Append(" BEGIN SET @Observacion = 'Ambiente Externo' END ")
                vsql.Append(" 					 ")
                vsql.Append(" IF (@cDocObservEntrada = '*' OR @cDocObservActual = '*') AND NOT (@cDocObservEntrada = '*' AND @cDocObservActual = '*') ")
                vsql.Append(" BEGIN ")
                vsql.Append(" IF @cDocObservEntrada = '*' ")
                vsql.Append(" BEGIN ")
                vsql.Append(" INSERT #ReportsIO (cPerCodigo,Nombres,cPerIdeNumero,nUniOrgCodigo,UniOrgNombre ,Fecha,MarcIngreso,MarcSalida,HorIngreso,HorSalida,Observacion,nTotalHoras) ")
                vsql.Append(" VALUES(@cPerCodigo,@cNombres,@cPerIdeNumero,@nUniOrgCodigo,@UniOrgNombre,@dHoraCompActual, ")
                vsql.Append(" CASE WHEN @nAmbienteExterno > 0 THEN @HorarioEntrada ELSE (CASE WHEN @nHorEstadoActual = 1 AND ISNULL(@cDocCodigoActual,'') = ''   THEN @HorarioEntrada ELSE (CASE WHEN @nHorEstadoActual = 1 AND ISNULL(@cDocCodigoActual,'') <> ''   THEN @HorarioEntrada ELSE NULL END)  END) END, ")
                vsql.Append(" CASE WHEN @nAmbienteExterno > 0 THEN @dHoraCompActual ELSE (CASE WHEN @nHorEstadoActual = 1 AND ISNULL(@cDocCodigoActual,'') = ''   THEN @dHoraActual ELSE (CASE WHEN @nHorEstadoActual = 1 AND ISNULL(@cDocCodigoActual,'') <> ''   THEN @dHoraCompActual ELSE NULL END)  END) END, ")
                vsql.Append(" @HorarioEntrada	,@dHoraCompActual,@Observacion,@nTotalHoras) ")
                vsql.Append(" END ")
                vsql.Append(" ELSE ")
                vsql.Append(" BEGIN ")
                vsql.Append(" INSERT #ReportsIO (cPerCodigo,Nombres,cPerIdeNumero,nUniOrgCodigo,UniOrgNombre ,Fecha,MarcIngreso,MarcSalida,HorIngreso	,HorSalida,Observacion,nTotalHoras) ")
                vsql.Append(" VALUES(@cPerCodigo,@cNombres,@cPerIdeNumero,@nUniOrgCodigo,@UniOrgNombre, ")
                vsql.Append(" @dHoraCompActual,CASE WHEN @nAmbienteExterno > 0 THEN @HorarioEntrada ELSE (CASE WHEN @nHorEstadoEntrada = 1 AND ISNULL(@cDocCodigoEntrada,'') = '' THEN @MarcEntrada ELSE (CASE WHEN @nHorEstadoEntrada = 1 AND ISNULL(@cDocCodigoEntrada,'') <> '' THEN @HorarioEntrada  ELSE NULL END)  END) END, ")
                vsql.Append(" CASE WHEN @nAmbienteExterno > 0 THEN @dHoraCompActual ELSE (CASE WHEN @nHorEstadoEntrada = 1 AND ISNULL(@cDocCodigoEntrada,'') = '' THEN @dHoraActual ELSE (CASE WHEN @nHorEstadoEntrada = 1 AND ISNULL(@cDocCodigoEntrada,'') <> '' THEN @dHoraCompActual  ELSE NULL END)  END) END, ")
                vsql.Append(" @HorarioEntrada	,@dHoraCompActual,@Observacion,@nTotalHoras) ")
                vsql.Append(" END ")
                vsql.Append(" END ELSE BEGIN ")
                vsql.Append(" INSERT #ReportsIO  ")
                vsql.Append(" (cPerCodigo,Nombres,cPerIdeNumero,nUniOrgCodigo,UniOrgNombre,Fecha,MarcIngreso,MarcSalida,HorIngreso,HorSalida	 ,Observacion,nTotalHoras) ")
                vsql.Append(" VALUES(@cPerCodigo,@cNombres,@cPerIdeNumero	,@nUniOrgCodigo,@UniOrgNombre,@dHoraCompActual, ")
                vsql.Append(" 	CASE WHEN @nAmbienteExterno > 0 THEN @HorarioEntrada ELSE (CASE WHEN @nHorEstadoEntrada = 1 AND ISNULL(@cDocCodigoEntrada,'') = '' THEN @MarcEntrada ELSE (CASE WHEN @nHorEstadoEntrada = 1 AND ISNULL(@cDocCodigoEntrada,'') <> '' THEN @HorarioEntrada  ELSE NULL END)  END) END, ")
                vsql.Append(" 	CASE WHEN @nAmbienteExterno > 0 THEN @dHoraCompActual ELSE (CASE WHEN @nHorEstadoActual = 1 AND ISNULL(@cDocCodigoActual,'') = ''   THEN @dHoraActual ELSE (CASE WHEN @nHorEstadoActual = 1 AND ISNULL(@cDocCodigoActual,'') <> ''   THEN @dHoraCompActual ELSE NULL END)  END) END, ")
                vsql.Append(" 	@HorarioEntrada	,@dHoraCompActual,@Observacion,@nTotalHoras) ")
                vsql.Append(" END ")
                vsql.Append(" UPDATE #ReportsIO SET nFlag = @nFlag WHERE nFlag IS NULL							 ")
                vsql.Append(" 	IF @nEstadoActual = 4 AND @nEstadoActual = 4 BEGIN SET @nFlag = @nFlag + 1 END							 ")
                vsql.Append(" 	SET @contador = 1 ")
                vsql.Append(" END ")
                vsql.Append(" FETCH NEXT FROM Marcaciones INTO @cNombres,@cPerIdeNumero,@dHoraActual,@nHorCodigoActual,@dHoraCompActual,@nHorEstadoActual, @cDocObservActual, @cDocCodigoActual, @nEstadoActual ")
                vsql.Append(" 	END ")
                vsql.Append(" 	CLOSE Marcaciones ")
                vsql.Append(" 	DEALLOCATE Marcaciones ")
                vsql.Append(" 	SET @dFechaIniBuscar = DATEADD(d,1,@dFechaIniBuscar) ")
                vsql.Append(" END ")
                vsql.Append(" ALTER TABLE #ReportsIO ADD nFlag2 INT ALTER TABLE #ReportsIO ADD nSec INT ")
                vsql.Append("  ")
                vsql.Append(" DECLARE @dHorIngreso datetime, @dHorSalida datetime,@dHorIngreso_Ant datetime, @dHorSalida_Ant datetime,@nFlag2 int=0, @num int=0 ")
                vsql.Append(" DECLARE Agrupar CURSOR FOR ")
                vsql.Append(" SELECT HorIngreso, HorSalida FROM #ReportsIO ORDER BY 1,2 ")
                vsql.Append(" OPEN Agrupar ")
                vsql.Append(" FETCH NEXT FROM Agrupar INTO @dHorIngreso, @dHorSalida ")
                vsql.Append(" WHILE @@FETCH_STATUS = 0 ")
                vsql.Append(" BEGIN ")
                vsql.Append(" UPDATE #ReportsIO SET  nSec = @num + 1 WHERE HorIngreso = @dHorIngreso AND HorSalida = @dHorSalida ")
                vsql.Append(" SET @num = @num + 1 ")
                vsql.Append(" IF NOT @dHorSalida_Ant IS NULL BEGIN ")
                vsql.Append(" 	IF DATEDIFF(mi,@dHorSalida_Ant,@dHorIngreso)<=45  ")
                vsql.Append(" 	  BEGIN UPDATE #ReportsIO SET nFlag2 = @nFlag2 WHERE HorIngreso = @dHorIngreso_Ant AND HorSalida = @dHorSalida_Ant END ")
                vsql.Append(" 	ELSE BEGIN ")
                vsql.Append(" 		UPDATE #ReportsIO SET nFlag2 = @nFlag2 WHERE HorIngreso = @dHorIngreso_Ant AND HorSalida = @dHorSalida_Ant ")
                vsql.Append(" 		SET @nFlag2 = @nFlag2 + 1 ")
                vsql.Append(" 	UPDATE #ReportsIO SET nFlag2 = @nFlag2 WHERE HorIngreso = @dHorIngreso AND HorSalida = @dHorSalida ")
                vsql.Append(" 	END ")
                vsql.Append(" END ")
                vsql.Append(" 	ELSE BEGIN SET @nFlag2 = 1 UPDATE #ReportsIO SET nFlag2 = @nFlag2 WHERE HorIngreso = @dHorIngreso AND HorSalida = @dHorSalida END ")
                vsql.Append(" SET @dHorIngreso_Ant = @dHorIngreso SET @dHorSalida_Ant = @dHorSalida ")
                vsql.Append(" FETCH NEXT FROM Agrupar INTO @dHorIngreso, @dHorSalida ")
                vsql.Append(" END ")
                vsql.Append(" CLOSE Agrupar ")
                vsql.Append(" DEALLOCATE Agrupar ")
                vsql.Append("  ")
                vsql.Append(" IF NOT @dHorSalida_Ant IS NULL ")
                vsql.Append(" BEGIN ")
                vsql.Append(" IF DATEDIFF(mi,@dHorSalida_Ant,@dHorIngreso)<=45  ")
                vsql.Append("   BEGIN UPDATE #ReportsIO SET nFlag2 = @nFlag2 WHERE HorIngreso = @dHorIngreso_Ant AND HorSalida = @dHorSalida_Ant END ")
                vsql.Append(" ELSE BEGIN ")
                vsql.Append(" 	UPDATE #ReportsIO SET nFlag2 = @nFlag2 WHERE HorIngreso = @dHorIngreso_Ant AND HorSalida = @dHorSalida_Ant ")
                vsql.Append(" 	SET @nFlag2 = @nFlag2 + 1  ")
                vsql.Append(" 	UPDATE #ReportsIO SET nFlag2 = @nFlag2 WHERE HorIngreso = @dHorIngreso AND HorSalida = @dHorSalida ")
                vsql.Append("   END ")
                vsql.Append(" END ELSE BEGIN ")
                vsql.Append(" 	SET @nFlag2 = 1 UPDATE #ReportsIO SET nFlag2 = @nFlag2 WHERE HorIngreso = @dHorIngreso AND HorSalida = @dHorSalida ")
                vsql.Append(" END ")
                vsql.Append(" SELECT nFlag2, MIN(nSec) as FlagMin, MAX(nSec) as FlagMax INTO #REF FROM #ReportsIO GROUP BY nFlag2 ")
                vsql.Append("  ")
                vsql.Append(" UPDATE #ReportsIO SET MarcIngreso = HorIngreso,MarcSalida = HorSalida ")
                vsql.Append(" WHERE (MarcIngreso IS NULL OR MarcSalida IS NULL) AND ")
                vsql.Append(" nFlag2 IN(SELECT DISTINCT nFlag2 FROM #ReportsIO R WHERE EXISTS (SELECT * FROM #REF A WHERE R.nFlag2 = A.nFlag2 AND R.nSec = A.FlagMax) AND NOT MarcSalida IS NULL) ")
                vsql.Append(" 	AND nSec NOT IN(SELECT nSec FROM #ReportsIO R WHERE nSec IN (SELECT FlagMin FROM #REF)) ")
                vsql.Append("  ")
                vsql.Append(" SELECT UniOrgNombre as cUniOrgNombre, ")
                vsql.Append(" 	convert(nchar(10),Fecha,103) AS dFecha,LEFT(CONVERT(VARCHAR(8), HorIngreso, 8), 5) as HorarioIngreso,LEFT(CONVERT(VARCHAR(8), HorSalida, 8), 5) as HorarioSalida,ISNULL(LEFT(CONVERT(VARCHAR(8), MarcIngreso, 8), 5),'-----') as Ingreso,ISNULL(LEFT(CONVERT(VARCHAR(8), MarcSalida, 8), 5),'-----') as Salida, ")
                vsql.Append(" 	(CASE WHEN MarcIngreso is not null and MarcSalida is not null  ")
                vsql.Append(" 		then (case when  MarcIngreso > HorIngreso then (datediff(mi,HorIngreso,MarcIngreso)*5)/6 else 0 end) + ")
                vsql.Append(" 			(case when  MarcSalida < HorSalida then (datediff(mi,MarcSalida,HorSalida)*5)/6 else 0 end) ")
                vsql.Append(" 		else DATEDIFF(mi,HorIngreso,HorSalida) end) as NoTrabajadas, ")
                vsql.Append(" CASE WHEN (MarcIngreso is null AND MarcSalida is null) THEN DATEDIFF(mi,HorIngreso,HorSalida) ELSE 0 END as Faltas, ")
                vsql.Append(" CASE WHEN (MarcIngreso is null AND MarcSalida is not null) OR (MarcIngreso is not null AND MarcSalida is null) THEN DATEDIFF(mi,HorIngreso,HorSalida) ELSE 0 END as Incompletas, ")
                vsql.Append(" CASE WHEN MarcIngreso is not null AND MarcSalida is not null AND (MarcIngreso>HorIngreso) THEN (DATEDIFF(mi,HorIngreso,MarcIngreso)*5)/6 ELSE 0 END as Tardanzas		 ")
                vsql.Append(" FROM #ReportsIO ORDER BY dFecha, HorarioIngreso ")
                vsql.Append(" DROP TABLE #ReportsIO DROP TABLE #Marca DROP TABLE #REF SET NOCOUNT OFF ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                'Db.AddInParameter(Cmd, "@cPerJuridica", System.Data.DbType.String, cPerCodigo)
                'Db.AddInParameter(Cmd, "@dFechaIni", System.Data.DbType.String, dFechaIni)
                'Db.AddInParameter(Cmd, "@dFechaFin", System.Data.DbType.String, dFechaFin)
                'Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_MarcacionesAdministrativo_By_cPerCodigo(ByVal cPerJuridica As String, ByVal dFechaIni As String, ByVal dFechaFin As String, ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" declare	@cPerJurCodigo char(10) declare	@cPerCodigo char(10) declare	@dfechaIni char(8) declare	@dfechaFin char(8) ")
                vsql.Append(" set	@cPerJurCodigo='" + cPerJuridica + "' set	@cPerCodigo='" + cPerCodigo + "' set	@dfechaIni='" + dFechaIni + "' set	@dfechaFin='" + dFechaFin + "' ")
                vsql.Append(" SET NOCOUNT ON SET DATEFORMAT dmy ")
                vsql.Append(" SELECT E1.cPerCodigo, Nombres,cPerIdeNumero,DATEPART(ww,E1.dHoraComp) AS nSemana,CONVERT(CHAR(10),E1.dHoraComp,103) as dfecha, ")
                vsql.Append(" (SELECT Distinct cUniOrgNombre FROM HorInterface HI WITH(NOLOCK) INNER JOIN UnidadOrganizacional UO ON hi.nObjCodigo=nUniOrgCodigo where hi.nHorCodigo=E1.nHorCodigo ) as UniOrgNombre, ")
                vsql.Append(" (CASE WHEN E1.nHorEstado=1 AND ISNULL(E1.cDocCodigo,'')='' THEN (CASE WHEN DATEDIFF(mi,E1.dHoraComp,E1.dHora) < 6 AND DATEDIFF(mi,E1.dHoraComp,E1.dHora)>0 AND E1.dHoraComp >= CAST('20100616' AS DATETIME) THEN E1.dHoraComp ELSE E1.dHora END) ELSE (CASE WHEN E1.nHorEstado=1 AND ISNULL(E1.cDocCodigo,'')<>'' THEN E1.dHoraComp ELSE NULL END) END) AS MarIngreso1, ")
                vsql.Append(" (CASE WHEN S1.nHorEstado=1 AND ISNULL(S1.cDocCodigo,'')='' THEN (CASE WHEN DATEDIFF(mi,S1.dHoraComp,S1.dHora) < 6 AND DATEDIFF(mi,S1.dHoraComp,S1.dHora)>0 AND S1.dHoraComp >= CAST('20100616' AS DATETIME) THEN S1.dHoraComp ELSE S1.dHora END) ELSE (CASE WHEN S1.nHorEstado=1 AND ISNULL(S1.cDocCodigo,'')<>'' THEN S1.dHoraComp ELSE NULL END) END) AS MarSalida1, ")
                vsql.Append(" (CASE WHEN E2.nHorEstado=1 AND ISNULL(E2.cDocCodigo,'')='' THEN (CASE WHEN DATEDIFF(mi,E2.dHoraComp,E2.dHora) < 6 AND DATEDIFF(mi,E2.dHoraComp,E2.dHora)>0 AND E2.dHoraComp >= CAST('20100616' AS DATETIME) THEN E2.dHoraComp ELSE E2.dHora END) ELSE (CASE WHEN E2.nHorEstado=1 AND ISNULL(E2.cDocCodigo,'')<>'' THEN E2.dHoraComp ELSE NULL END) END) AS MarIngreso2, ")
                vsql.Append(" (CASE WHEN S2.nHorEstado=1 AND ISNULL(S2.cDocCodigo,'')='' THEN (CASE WHEN DATEDIFF(mi,S2.dHoraComp,S2.dHora) < 6 AND DATEDIFF(mi,S2.dHoraComp,S2.dHora)>0 AND S2.dHoraComp >= CAST('20100616' AS DATETIME) THEN S2.dHoraComp ELSE S2.dHora END) ELSE (CASE WHEN S2.nHorEstado=1 AND ISNULL(S2.cDocCodigo,'')<>'' THEN S2.dHoraComp ELSE NULL END) END) AS MarSalida2, ")
                vsql.Append(" E1.dHoraComp AS HorIngreso1,S1.dHoraComp AS HorSalida1,E2.dHoraComp AS HorIngreso2,S2.dHoraComp AS HorSalida2, ")
                vsql.Append(" ISNULL((E1.cDocObserv + (CASE WHEN S1.cDocObserv<>E1.cDocObserv  THEN  '-'+S1.cDocObserv ELSE '' END)+ (CASE WHEN E2.cDocObserv<>S1.cDocObserv  THEN  '-'+E2.cDocObserv ELSE '' END)+ ")
                vsql.Append(" (CASE WHEN S2.cDocObserv<>E2.cDocObserv  THEN  '-'+S2.cDocObserv ELSE '' END)),'') AS cDocObserv INTO #ReportsIO FROM  ")
                vsql.Append(" (SELECT h.cPerCodigo,h.dHora,h.nTipo,h.nHorEstado,h.dHoraComp,h.cDocObserv,h.nHorCodigo,h.cDocCodigo ")
                vsql.Append(" FROM HoraIO h WITH(NOLOCK) INNER JOIN Horario ho WITH(NOLOCK) ON h.nHorCodigo=ho.nHorCodigo ")
                vsql.Append(" WHERE cPerCodigo=@cPerCodigo AND dHora>=CONVERT(DATETIME, @dfechaIni, 101) AND dHora<=CONVERT(DATETIME, @dfechaFin, 101) ")
                vsql.Append(" AND cPerJurCodigo=@cPerJurCodigo  AND ho.nHortipo IN(1,3) and nTipo=1) AS E1 ")
                vsql.Append(" INNER JOIN (SELECT h.cPerCodigo,h.dHora,h.nTipo,h.nHorEstado,h.dHoraComp,h.cDocObserv,h.cDocCodigo ")
                vsql.Append(" FROM HoraIO h WITH(NOLOCK) INNER JOIN Horario ho WITH(NOLOCK) ON h.nHorCodigo=ho.nHorCodigo ")
                vsql.Append(" WHERE cPerCodigo=@cPerCodigo AND dHora>=CONVERT(DATETIME, @dfechaIni, 101) AND dHora<=CONVERT(DATETIME, @dfechaFin, 101) ")
                vsql.Append(" AND cPerJurCodigo=@cPerJurCodigo AND ho.nHortipo IN(1,3) and nTipo=2) AS S1 ")
                vsql.Append(" ON	CONVERT(CHAR(10),E1.dHoraComp,103)=CONVERT(CHAR(10),S1.dHoraComp,103) ")
                vsql.Append(" LEFT JOIN (SELECT h.cPerCodigo,h.dHora,h.nTipo,h.nHorEstado,h.dHoraComp,h.cDocObserv,h.cDocCodigo ")
                vsql.Append(" FROM HoraIO h WITH(NOLOCK) INNER JOIN Horario ho WITH(NOLOCK) ON h.nHorCodigo=ho.nHorCodigo ")
                vsql.Append(" WHERE cPerCodigo=@cPerCodigo AND dHora>=CONVERT(DATETIME, @dfechaIni, 101) AND dHora<=CONVERT(DATETIME, @dfechaFin, 101) ")
                vsql.Append(" AND cPerJurCodigo=@cPerJurCodigo AND ho.nHortipo IN(1,3) and nTipo=3) AS E2 ")
                vsql.Append(" ON	CONVERT(CHAR(10),E1.dHoraComp,103)=CONVERT(CHAR(10),E2.dHoraComp,103) ")
                vsql.Append(" LEFT JOIN (SELECT h.cPerCodigo,h.dHora,h.nTipo,h.nHorEstado,h.dHoraComp,h.cDocObserv,h.cDocCodigo ")
                vsql.Append(" FROM HoraIO h WITH(NOLOCK) INNER JOIN Horario ho WITH(NOLOCK) ON h.nHorCodigo=ho.nHorCodigo ")
                vsql.Append(" WHERE cPerCodigo=@cPerCodigo AND dHora>=CONVERT(DATETIME, @dfechaIni, 101) AND dHora<=CONVERT(DATETIME, @dfechaFin, 101) ")
                vsql.Append(" AND cPerJurCodigo=@cPerJurCodigo AND ho.nHortipo IN(1,3) and nTipo=4) AS S2 ")
                vsql.Append(" ON	CONVERT(CHAR(10),E1.dHoraComp,103)=CONVERT(CHAR(10),S2.dHoraComp,103) ")
                vsql.Append(" INNER JOIN (SELECT p.cPerCodigo,rtrim(cPerApellido) +' '+rtrim(cPerNombre) as Nombres,cPerIdeNumero  ")
                vsql.Append(" FROM Persona P WITH(NOLOCK) INNER JOIN PerIdentifica pid WITH(NOLOCK) ON p.cPerCodigo=pid.cPerCodigo ")
                vsql.Append(" WHERE nPerIdeTipo IN (22,25,28) and p.cPerCodigo=@cPerCodigo ) p  ")
                vsql.Append(" ON E1.cPerCodigo=p.cPerCodigo ")
                vsql.Append(" SELECT UniOrgNombre,dFecha,LEFT(CONVERT(VARCHAR(8), MarIngreso1, 8), 5) as HI1,LEFT(CONVERT(VARCHAR(8), MarSalida1, 8), 5)  as HS1,LEFT(CONVERT(VARCHAR(8), MarIngreso2, 8), 5)  as HI2,LEFT(CONVERT(VARCHAR(8), MarSalida2, 8), 5)  AS HS2, ")
                vsql.Append(" ((CASE WHEN MarIngreso1 is not null and MarSalida1 is not null then (case when  MarIngreso1 > HorIngreso1 then datediff(mi,HorIngreso1,MarIngreso1) else 0 end) + ")
                vsql.Append(" (case when  MarSalida1 < HorSalida1 then datediff(mi,MarSalida1,HorSalida1) else 0 end)	 ")
                vsql.Append(" else DATEDIFF(mi,HorIngreso1,HorSalida1) end )+ ")
                vsql.Append(" (CASE WHEN MarIngreso2 is not null and MarSalida2 is not null then (case when  MarIngreso2 > HorIngreso2 then datediff(mi,HorIngreso2,MarIngreso2) else 0 end) + ")
                vsql.Append(" (case when  MarSalida2 < HorSalida2 then datediff(mi,MarSalida2,HorSalida2) else 0 end)	 ")
                vsql.Append(" else (CASE WHEN HorSalida2 IS NOT NULL THEN DATEDIFF(mi,HorIngreso2,HorSalida2) ELSE 0 END) end ) ) AS NoTrabajadas, ")
                vsql.Append(" RTRIM(LTRIM(cDocObserv)) as Observacion FROM #ReportsIO ")
                vsql.Append(" ORDER BY Nombres, nSemana, CAST(dFecha AS DATETIME) ")
                vsql.Append(" DROP TABLE #ReportsIO ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                'Db.AddInParameter(Cmd, "@cPerJuridica", System.Data.DbType.String, cPerCodigo)
                'Db.AddInParameter(Cmd, "@dFechaIni", System.Data.DbType.String, dFechaIni)
                'Db.AddInParameter(Cmd, "@dFechaFin", System.Data.DbType.String, dFechaFin)
                'Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_HorarioDocente_By_cPerCodigo(ByVal cPerCodigo As String, ByVal nPrdCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_RegAca_Get_HorarioCorto_By_Alumno")
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "@nPrdCodigo", System.Data.DbType.Int32, nPrdCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_PlanEstudios_By_nCurCodigo(ByVal cOpcion As String, ByVal nCurCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_RA_Get_PlanEst_by_UniOrg_Cur")
                Db.AddInParameter(Cmd, "@cOpcion", System.Data.DbType.String, cOpcion)
                Db.AddInParameter(Cmd, "@nCurCodigo", System.Data.DbType.Int32, nCurCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Eventos_By_NuniOrgcodigo(ByVal nUniOrgCodigo As Integer, ByVal Tipo As Long, ByVal Opcion As Integer) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_Eventos_Get_By_nUniOrgCodigo")
                Db.AddInParameter(Cmd, "@nUniOrgCodigo", System.Data.DbType.String, nUniOrgCodigo)
                Db.AddInParameter(Cmd, "@nDocTipo", System.Data.DbType.Int32, Tipo)
                Db.AddInParameter(Cmd, "@nTop", System.Data.DbType.Int32, Opcion)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Eventos_By_cDocCodigo(ByVal cDocCodigo As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_Noticias_Eventos_Get_By_cDocCodigo")
                Db.AddInParameter(Cmd, "@cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_ServiciosUniversitarios_By_nUniOrgCodigo(ByVal nUniOrgCodigo As Integer, ByVal cIntJerarquia As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("spWeb_Portal_Get_SubMenu_By_UniOrg_Jerarquia")
                Db.AddInParameter(Cmd, "@nUniOrgCodigo", System.Data.DbType.Int32, nUniOrgCodigo)
                Db.AddInParameter(Cmd, "@cIntJerarquia", System.Data.DbType.String, cIntJerarquia)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Expediente_By_cPercodigo_nCurCodigo(ByVal cPerCodigo As String, ByVal nPrdAdmCodigo As Integer) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" SELECT DISTINCT PAR.nPerAdmReqTipo,R.cIntNombre  ,PAR.bPerAdmReqOk  ,PAR.nReqStatus    ,C.cConDescripcion, ")
                vsql.Append(" Estado=case when PAR.bPerAdmReqOk=0 then 'No' else 'Si' end ")
                vsql.Append(" FROM PerAdmRequisito PAR ")
                vsql.Append(" INNER JOIN Interface  R ON PAR.nPerAdmReqTipo = R.nIntCodigo and R.nIntClase =6024 ")
                vsql.Append(" LEFT OUTER JOIN Constante C ON PAR.nReqStatus = C.nConValor AND C.nConCodigo   = 2026 ")
                vsql.Append(" WHERE PAR.cPerCodigo = '" + cPerCodigo.ToString + "' AND PAR.nprdCodigo =" + nPrdAdmCodigo.ToString + " ")
                vsql.Append(" ORDER BY PAR.bPerAdmReqOk DESC,PAR.nPerAdmReqTipo ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function
        Public Function Get_Expedientes_By_cPerCodigo_nPrdCodigoAdm(ByVal cPerCodigo As String, ByVal nPrdCodigoAdm As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_GetExpedientes_By_cPerCodigo_nPrdCodigoAdm")
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "@nPrdCodigoAdm", System.Data.DbType.Int32, nPrdCodigoAdm)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function
        Public Function Get_BoletaNotas_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" Declare @cPercodigo nchar(10)  set @cPercodigo='" + cPerCodigo.ToString + "' ")
                vsql.Append(" SELECT distinct Curso.cDocCodigo, Curso.nCicCodigo,Asignatura.cAsiDescripcion, Curso.nAsiCodigo, Curso.nMenCodigo, Curso.cCurDetCod,  ")
                vsql.Append(" Curso.nCurDetCredito,Curso.nCurCodigo,Curso.nSecCodigo, ")
                vsql.Append(" isnull((SELECT TOP 1 nValor FROM DocPerNotaPrd WHERE (nCurCodigo = Curso.nCurCodigo) And (nCicCodigo = Curso.nCicCodigo) And  ")
                vsql.Append(" (nMenCodigo = Curso.nMenCodigo) AND (nAsiCodigo = Curso.nAsiCodigo) AND (cDocCodigo = Curso.cDocCodigo) AND (nSubPrdCodigo = 1)),'') AS P1 ,  ")
                vsql.Append(" isnull((SELECT TOP 1 nValor FROM DocPerNotaPrd WHERE (nCurCodigo = Curso.nCurCodigo) And (nCicCodigo = Curso.nCicCodigo) And  ")
                vsql.Append(" (nMenCodigo = Curso.nMenCodigo) AND (nAsiCodigo = Curso.nAsiCodigo) AND (cDocCodigo = Curso.cDocCodigo) AND (nSubPrdCodigo = 2)),'') AS P2 ,  ")
                vsql.Append(" isnull((SELECT TOP 1 nValor FROM DocPerNotaPrd WHERE (nCurCodigo = Curso.nCurCodigo) And (nCicCodigo = Curso.nCicCodigo) And  ")
                vsql.Append(" (nMenCodigo = Curso.nMenCodigo) AND (nAsiCodigo = Curso.nAsiCodigo) AND (cDocCodigo = Curso.cDocCodigo) AND (nSubPrdCodigo = 3)),'') AS P3 ,  ")
                vsql.Append(" ISNULL(fMatCurNota,0) Promedio,Curso.cMatCurTipo as TipoCurso,G.nCurGruCodigo   ")
                vsql.Append(" FROM (SELECT DPC.cDocCodigo, DPC.nCurCodigo, DPC.nCicCodigo, DPC.nAsiCodigo, ISNULL(CDE.nAsiCodigoE, CD.nAsiCodigo) AS nAsiCodigoE,  ")
                vsql.Append(" DPC.nMenCodigo, ISNULL(CDE.cCurDetCod, CD.cCurDetCod) AS cCurDetCod, DPC.nSecCodigo, CD.nCurDetCredito, DPC.nDocPerCurVez, DPC.fMatCurNota,  ")
                vsql.Append(" DPC.fMatCurExa, DPC.fMatCurAplaz, DPC.cMatCurEstado, DPC.nMatCurTipo,isnull(dpc.cMatCurTipo,'O')cMatCurTipo FROM DocPerCursos DPC  ")
                vsql.Append(" INNER JOIN CurDetalle CD ON DPC.nCurCodigo = CD.nCurCodigo AND DPC.nCicCodigo = CD.nCicCodigo AND DPC.nAsiCodigo = CD.nAsiCodigo  ")
                vsql.Append(" INNER JOIN DocPersona dper on DPC.cDocCodigo =dper.cDocCodigo and dper.nDocPerTipo =1 ")
                vsql.Append(" INNER JOIN DocPeriodo dp on DPC.cDocCodigo =dp.cDocCodigo  ")
                vsql.Append(" INNER JOIN Periodo Per on dp.nPrdIni =Per.nPrdCodigo and Per.nPrdActividad =2 and Per.nPrdEstado=1 ")
                vsql.Append(" LEFT OUTER JOIN CurDetEspecial CDE ON DPC.nCurCodigo = CDE.nCurCodigo AND DPC.nCicCodigo = CDE.nCicCodigo AND DPC.nAsiCodigo = CDE.nAsiCodigo And DPC.nMenCodigo = CDE.nMenCodigo  ")
                vsql.Append(" WHERE CD.nCurDetTipo<> 0 and DPC.cMatCurEstado <> 5 AND dper.cPerCodigo =@cPercodigo) Curso  ")
                vsql.Append(" INNER JOIN ( SELECT nConValor,cConDescripcion FROM Constante  C WHERE nConCodigo = 1093) Seccion ON Curso.nSecCodigo = Seccion.nConValor  ")
                vsql.Append(" INNER JOIN DocPerGrupo DPG ON Curso.cDocCodigo =DPG.cDocCodigo  ")
                vsql.Append(" INNER JOIN Grupos G on DPG.nGruCodigo =G.nGruCodigo ")
                vsql.Append(" INNER JOIN CurGrupo CG on G.nCurGruCodigo =CG.nCurGruCodigo and Curso.nCurCodigo =CG.nCurCodigo and Curso.nCicCodigo =CG.nCicCodigo and Curso.nAsiCodigo =CG.nAsiCodigo  ")
                vsql.Append(" INNER JOIN Asignatura ON Curso.nAsiCodigoE = Asignatura.nAsiCodigo  ")
                vsql.Append(" LEFT OUTER JOIN ( SELECT nConValor,cConDescripcion FROM Constante  C WHERE nConCodigo = 1050) Ciclo ON Curso.nCicCodigo = Ciclo.nConValor  ")
                vsql.Append(" LEFT OUTER JOIN CurDenominacion CurDen ON Curso.nCurCodigo = CurDen.nCurCodigo AND Curso.nCicCodigo = CurDen.nCicCodigo  ")
                vsql.Append(" ORDER BY Curso.cMatCurTipo desc,Curso.nCicCodigo,Curso.cCurDetCod  ")


                'Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_Get_BoletaNotas_By_cPerCodigo")
                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                'Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_HorarioAlumno_By_cPerCodigo(ByVal cPerCodigo As String, ByVal nsemana As Long) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_Get_HorarioAlumno_By_cPerCodigo")
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "@nSemana", System.Data.DbType.Int32, nsemana)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_AsistenciaAlumno_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_Get_AsistenciaAlumno_By_cPerCodigo")
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_NombrePariente_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As String
                vsql = "select cperapellido + ' ' + cpernombre as NombreCompleto from persona where cpercodigo='" + cPerCodigo + "'"
                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Filial_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" select distinct uo.cPerJuridica,uo.cPerApellido  from PerAluRegistra palu ")
                vsql.Append(" inner join UnidadOrganizacional uo on palu.nUniOrgCodigo =uo.nUniOrgCodigo  ")
                vsql.Append(" where palu.cPerCodigo ='" + cPerCodigo + "' and nPerAluRegEstado in (1,2) ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_ServiciosUniversitarios_Tema(ByVal nIntClase As Integer, ByVal nIntTipo As Integer, ByVal nUniOrgCodigo As Integer, _
                                                         ByVal nDoctipo As Integer, ByVal nEstado As Integer, ByVal nTop As Integer, Optional ByVal nIntCodigo As String = "") As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("spWeb_Portal_Get_Documentos_By_UniOrg")
                Db.AddInParameter(Cmd, "@nIntClase", System.Data.DbType.Int32, nIntClase)
                Db.AddInParameter(Cmd, "@nIntTipo", System.Data.DbType.Int32, nIntTipo)
                Db.AddInParameter(Cmd, "@nUniOrgCodigo", System.Data.DbType.Int32, nUniOrgCodigo)
                Db.AddInParameter(Cmd, "@nDoctipo", System.Data.DbType.Int32, nDoctipo)
                Db.AddInParameter(Cmd, "@nEstado", System.Data.DbType.Int32, nEstado)
                Db.AddInParameter(Cmd, "@nTop", System.Data.DbType.Int32, nTop)
                'Db.AddInParameter(Cmd, "@nIntCodigo", System.Data.DbType.Int32, nIntCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_ServiciosUniversitarios_NombreEscuela(ByVal nUniOrgcodigo As Integer) As DbCommand
            Try
                Dim vsql As String
                vsql = "SELECT nUniOrgCodigo, cUniOrgAbrev FROM UnidadOrganizacional where nUniOrgCodigo=" + nUniOrgcodigo.ToString
                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_ServiciosUniversitarios_DatosEscuela(ByVal nUniOrgcodigo As Integer) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("spWeb_Portal_Get_DatosUniOrg_By_UniOrg")
                Db.AddInParameter(Cmd, "@nUniOrgcodigo", System.Data.DbType.Int32, nUniOrgcodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_HorarioDocente_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" declare	@cPerCodigo varchar(18) declare	@nsemana int = NULL set @cPerCodigo='" + cPerCodigo + "' ")
                vsql.Append(" SELECT @nsemana=DATEPART( wk, GETDATE() ) ")
                vsql.Append(" SELECT DISTINCT * FROM (SELECT  UniOrg.cIntNombre AS cUniOrgAbrev,H.dHorInicio,H.dHorFin,h.observ AS Observ ,AMBIENTE.AMBIENTE, ")
                vsql.Append(" con.cConDescripcion as seccion FROM    dbo.Horario AS H ")
                vsql.Append(" INNER JOIN dbo.HorInterface AS HI ON H.nHorCodigo = HI.nHorCodigo ")
                vsql.Append(" LEFT JOIN (SELECT  DI.cDocCodigo,DI.nIntCodigo,DI.nIntClase,dbo.Persona.cPerCodigo,dbo.Persona.cPerApellido + ' ' + dbo.Persona.cPerNombre AS Reservador ")
                vsql.Append(" FROM    dbo.DocInterface AS DI INNER JOIN dbo.DocPersona AS DP ON DI.cDocCodigo = DP.cDocCodigo ")
                vsql.Append(" INNER JOIN dbo.Persona ON DP.cPerCodigo = dbo.Persona.cPerCodigo ")
                vsql.Append(" WHERE DI.nIntClase = 2503 AND DP.nDocPerTipo = 1) AS Da ON H.nHorCodigo = Da.nIntCodigo ")
                vsql.Append(" INNER JOIN (SELECT  nConValor,cConDescripcion FROM dbo.Constante WHERE nConCodigo = 1115) AS Estado ON H.nHorEstado = Estado.nConValor ")
                vsql.Append(" LEFT OUTER JOIN (SELECT  I.nIntClase ,I.cIntNombre,HI.nHorCodigo FROM dbo.HorInterface AS HI ")
                vsql.Append(" INNER JOIN dbo.Interface AS I ON HI.nObjCodigo = I.nIntCodigo AND HI.nObjTipo   = I.nIntClase ")
                vsql.Append(" WHERE I.nIntClase = 1006) AS UniOrg ON H.nHorCodigo = UniOrg.nHorCodigo ")
                vsql.Append(" LEFT OUTER JOIN dbo.Documentos ON Da.cDocCodigo = dbo.Documentos.cDocCodigo ")
                vsql.Append(" INNER JOIN (SELECT  BienRegistro.nbieregcodigo,cintdescripcion AS AMBIENTE ")
                vsql.Append(" FROM (SELECT  nIntCodigo,cIntDescripcion FROM Interface WHERE nIntClase = 6000) AS Bien ")
                vsql.Append(" INNER JOIN BienRegistro ON Bien.nIntCodigo = BienRegistro.nBieCodigo) AMBIENTE ")
                vsql.Append(" ON AMBIENTE.NBIEREGCODIGO=HI.NOBJCODIGO ")
                vsql.Append(" INNER JOIN HorInterface HID ON HID.nHorCodigo=H.nHorCodigo AND HID.nobjtipo  =5004 INNER JOIN grupos G ON G.nGruCodigo=HID.nobjcodigo ")
                vsql.Append(" INNER JOIN CurGrupo CG ON CG.nCurGruCodigo=G.nCurGruCodigo INNER JOIN Asignatura A ON A.nAsiCodigo=cg.nAsiCodigo ")
                vsql.Append(" INNER JOIN constante con ON con.nconvalor =CG.nseccodigo AND con.nconcodigo=1093 ")
                vsql.Append(" WHERE HI.nObjTipo = 6006 AND da.cPerCodigo=@cPerCodigo AND H.nhorestado<>99  ")
                vsql.Append(" and CG.nPrdCodigo in (select nPrdCodigo from Periodo where nPrdActividad =2 and nPrdEstado =1) and ")
                vsql.Append(" ((DATEPART(wk, H.dHorInicio) = @nsemana) OR (DATEPART(wk, H.dHorFin) = @nsemana))) Hor ORDER BY Hor.dHorInicio,Hor.dHorFin ")


                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_HorarioAdministrativo_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim vsql As New StringBuilder
                vsql.Append(" set language spanish ")
                vsql.Append(" Declare @cDocCodigoContrato as varchar(18) Declare @cDocCodigoHorario as varchar(18) declare @nsemana int = NULL Declare @cPercodigo nchar(10) ")
                vsql.Append(" set @cPercodigo='" + cPerCodigo + "' SELECT @nsemana=DATEPART( wk, GETDATE() )  ")
                vsql.Append(" SELECT @cDocCodigoContrato=Documentos.cDocCodigo FROM DocPersona WITH(NOLOCK)   ")
                vsql.Append(" INNER JOIN Documentos WITH(NOLOCK) INNER JOIN DocLaboral WITH(NOLOCK) ON Documentos.cDocCodigo = DocLaboral.cDocCodigo  ")
                vsql.Append(" INNER JOIN Constante Estado WITH(NOLOCK) ON Documentos.nEstado = Estado.nConValor  ")
                vsql.Append(" INNER JOIN Constante Status WITH(NOLOCK) ON DocLaboral.nCtoStatus = Status.nConValor ON DocPersona.cDocCodigo = Documentos.cDocCodigo  ")
                vsql.Append(" AND DocPersona.nDocPerTipo = 1  ")
                vsql.Append(" LEFT OUTER JOIN DocVigencia WITH(NOLOCK) ON Documentos.cDocCodigo = DocVigencia.cDocCodigo   ")
                vsql.Append(" INNER JOIN DocMotivo DM WITH(NOLOCK) ON Documentos.cDocCodigo = DM.cDocCodigo AND DM.nDocMotivo = 4113   ")
                vsql.Append(" WHERE Estado.nConCodigo = 1066 AND Status.nConCodigo = 1085 AND DocPersona.cPerCodigo=@cPercodigo AND Estado.cConDescripcion='VIGENTE' ")
                vsql.Append(" SELECT top 1 @cDocCodigoHorario=d.cDocCodigo  FROM Documentos D WITH(NOLOCK)  ")
                vsql.Append(" INNER JOIN DocRef DR WITH(NOLOCK) ON D.cDocCodigo=DR.cDocCodigo   ")
                vsql.Append(" INNER JOIN DocVigencia DV WITH(NOLOCK) ON D.cDocCodigo=DV.cDocCodigo ")
                vsql.Append(" WHERE dr.cDocRefCodigo =@cDocCodigoContrato AND d.nDocTipo= 2503  ")
                vsql.Append(" ORDER BY DV.dFechaFin desc ")
                vsql.Append("  ")
                vsql.Append(" SELECT  DATENAME(dw, dHorInicio)+ ',' + SPACE(1) + left(DATENAME(m, dHorInicio),3)+ SPACE(1) + CAST(DAY(dHorInicio) AS VARCHAR(2))+ ',' + SPACE(1) + CAST(YEAR(dHorInicio) AS CHAR(4))	 as Fecha,LEFT(CONVERT(VARCHAR(8), dHorInicio, 8), 5) as HorIni,LEFT(CONVERT(VARCHAR(8), dHorFin, 8), 5) as HorFin,cUniOrgNombre FROM Horario H WITH(NOLOCK)   ")
                vsql.Append(" INNER JOIN DocInterface DI WITH(NOLOCK) ON h.nHorCodigo=di.nIntCodigo AND DI.nIntClase=2503  ")
                vsql.Append(" INNER JOIN HorInterface HI WITH(NOLOCK) ON H.nHorCodigo=HI.nHorCodigo and HI.nObjTipo=1006  ")
                vsql.Append(" INNER JOIN UnidadOrganizacional UO ON hi.nObjCodigo=uo.nUniOrgCodigo   ")
                vsql.Append(" WHERE cDocCodigo=@cDocCodigoHorario And H.nHorTipo IN(1,3) and ((DATEPART(wk, H.dHorInicio) = @nsemana) and (DATEPART(wk, H.dHorFin) = @nsemana)) ORDER BY dHorInicio ")

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(vsql.ToString)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_AsistenciaDocente_By_PerJuridica_cPerCodigo_DInicio_DFin(ByVal cPerJuridica As String, ByVal cPerCodigo As String, ByVal DInicio As String, ByVal DFin As String) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_Horario_Get_Asistencia_Docentes")
                Db.AddInParameter(Cmd, "@cPerJurCodigo", System.Data.DbType.String, cPerJuridica)
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "@dFechaIni", System.Data.DbType.String, DInicio)
                Db.AddInParameter(Cmd, "@dFechaFin", System.Data.DbType.String, DFin)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function
        Public Function Get_AsistenciaAdministrativo_By_PerJuridica_cPerCodigo_DInicio_DFin(ByVal cPerJuridica As String, ByVal cPerCodigo As String, ByVal DInicio As String, ByVal DFin As String) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_Horario_Get_Asistencia_Persona")
                Db.AddInParameter(Cmd, "@cPerJurCodigo", System.Data.DbType.String, cPerJuridica)
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "@dFechaIni", System.Data.DbType.String, DInicio)
                Db.AddInParameter(Cmd, "@dFechaFin", System.Data.DbType.String, DFin)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function
        Public Function Get_HorarioAdministrativo_By_cPerCodigo(ByVal cPerCodigo As String, ByVal nsemana As Long) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_Horario_Administrativo_Get_By_CPerCodigo_Semana")
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "@nSemana", System.Data.DbType.Int32, nsemana)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function
        Public Function Get_Apoderados_By_cPerCodigoPadre(ByVal cPerCodigo As String) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("Sp_Kiosco_Get_Parentescos_By_cPerCodigo")
                Db.AddInParameter(Cmd, "@cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_UO_by_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Using cmd As DbCommand = Db.GetStoredProcCommand("usp_campus_get_uo_by_cPerCodigo")
                    Db.AddInParameter(cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                    Return cmd
                End Using
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_TipoCurricula_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try
                Using cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_TipoCurricula_By_cPerCodigo")
                    Db.AddInParameter(cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                    Return cmd
                End Using
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Curriculas_By_cPerCodigo_nTipCur(ByVal cPerCodigo As String, ByVal nTipCur As Long) As DbCommand
            Try
                Using cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_Curriculas_By_cPerCodigo_nTipCur")
                    Db.AddInParameter(cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                    Db.AddInParameter(Cmd, "nTipCur", System.Data.DbType.Int32, nTipCur)
                    Return cmd
                End Using
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_CursosMatriculados_By_cPerCodigo_nCurcodigo_nTipCur(ByVal cPerCodigo As String, ByVal nTipCur As Long, ByVal nCurCodigo As Long) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_CursosMatriculados_By_cPerCodigo_nCurcodigo_nTipCur")
                Db.AddInParameter(Cmd, "cperCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nTipCur", System.Data.DbType.Int32, nTipCur)
                Db.AddInParameter(Cmd, "nCurCodigo", System.Data.DbType.Int32, nCurCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try

        End Function

        Public Function Get_CursosMatriculadosPDP_By_cPerCodigo_nCurcodigo_nTipCur(ByVal cPerCodigo As String, ByVal nTipCur As Long, ByVal nCurCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_CursosMatriculadosPDP_By_cPerCodigo_nCurcodigo_nTipCur")
                Db.AddInParameter(Cmd, "cperCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nTipCur", System.Data.DbType.Int32, nTipCur)
                Db.AddInParameter(Cmd, "nCurCodigo", System.Data.DbType.Int32, nCurCodigo)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_CursosMatriculadosPostgrado_By_cPerCodigo_nCurcodigo_nTipCur(ByVal cPerCodigo As String, ByVal nTipCur As Long, ByVal nCurCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_CursosMatriculadosPostGrado_By_cPerCodigo_nCurcodigo_nTipCur")
                Db.AddInParameter(Cmd, "cperCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nTipCur", System.Data.DbType.Int32, nTipCur)
                Db.AddInParameter(Cmd, "nCurCodigo", System.Data.DbType.Int32, nCurCodigo)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_CursosMatriculadosExtra_By_cPerCodigo_nCurcodigo_nTipCur(ByVal cPerCodigo As String, ByVal nTipCur As Long, ByVal nCurCodigo As Long) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_CursosMatriculadosExtra_By_cPerCodigo_nCurcodigo_nTipCur")
                Db.AddInParameter(Cmd, "cperCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nTipCur", System.Data.DbType.Int32, nTipCur)
                Db.AddInParameter(Cmd, "nCurCodigo", System.Data.DbType.Int32, nCurCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try

        End Function


        Public Function Get_InformacionCurso_By_nCurGruCodigo(ByVal nCurGruCodigo As String) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_InformacionCurso_By_nCurGruCodigo")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.String, nCurGruCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try

        End Function

        Public Function Get_Anuncio_By_nCurGruCodigo(ByVal nGruCodigo As Long) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_Anuncio_By_nCurGruCodigo")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nGruCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_ConsultasAndRespuestas(ByVal nCurGruCodigo As Long, ByVal nTipo As Integer, ByVal nEstado As Integer, ByVal cPerCodigo As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_ConsultasAndRespuestas")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Db.AddInParameter(Cmd, "nTipo", System.Data.DbType.Int32, nTipo)
                Db.AddInParameter(Cmd, "nHelEstado", System.Data.DbType.Int32, nEstado)
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_Asignatura_by_nCurGruCodigo(ByVal nCurGruCodigo As String) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_Asignatura_by_nCurGruCodigo")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.String, nCurGruCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try

        End Function

        Public Function Get_DocLink_By_cDocCodigo_nItem(ByVal cDocCodigo As String, ByVal nDocLinGrupo As Integer) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_DocLink_By_cDocCodigo_nItem")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Db.AddInParameter(Cmd, "nItem", System.Data.DbType.Int32, nDocLinGrupo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_DocLink_By_cDocCodigo_nDocLinNum(ByVal cDocCodigo As String, ByVal nDocLin As Integer) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_DocLink_By_cDocCodigo_nDocLinNum")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Db.AddInParameter(Cmd, "nDocLin", System.Data.DbType.Int32, nDocLin)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_ForoCampus_By_nCurGruCodigo_nForoPadre_nForoPrincipal(ByVal nCurGruCodigo As Long, ByVal cForoPadre As String, _
                                                                                  ByVal cForoPrincipal As String, ByVal nestado As Integer) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_ForoCampus_By_nCurGruCodigo_nForoPadre_nForoPrincipal")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Db.AddInParameter(Cmd, "cForoPadre", System.Data.DbType.String, cForoPadre)
                Db.AddInParameter(Cmd, "cForoPrincipal", System.Data.DbType.String, cForoPrincipal)
                Db.AddInParameter(Cmd, "nestado", System.Data.DbType.Int32, nestado)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_CursosAsignados_Pregrado_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_CursosAsignados_Pregrado_By_cPerCodigo")
                Db.AddInParameter(Cmd, "cperCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_CursosAsignados_Postgrado_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_CursosAsignados_Postgrado_By_cPerCodigo")
                Db.AddInParameter(Cmd, "cperCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Get_CursosAsignados_ExtraCurriculares_By_cPerCodigo(ByVal cPerCodigo As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Get_CursosAsignados_ExtraCurriculares_By_cPerCodigo")
                Db.AddInParameter(Cmd, "cperCodigo", System.Data.DbType.String, cPerCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

#End Region

#Region "Insert"

        Public Function Ins_Archivo(ByVal cDocCodigo As String, ByVal cPerCodigo As String, ByVal nPerRelacion As Integer, _
                                                                       ByVal nDocLinTipo As Integer, ByVal nDocLinGrupo As Integer, ByVal cDocLinUrl As String, ByVal cDocLinDescripcion As String, _
                                                                       Optional ByVal nCarCodigo As Integer = 1) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Ins_Recurso")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nPerRelacion", System.Data.DbType.Int32, nPerRelacion)
                Db.AddInParameter(Cmd, "nDocLinTipo", System.Data.DbType.Int32, nDocLinTipo)
                Db.AddInParameter(Cmd, "nDocLinGrupo", System.Data.DbType.Int32, nDocLinGrupo)
                Db.AddInParameter(Cmd, "cDocLinUrl", System.Data.DbType.String, cDocLinUrl)
                Db.AddInParameter(Cmd, "cDocLinDescripcion", System.Data.DbType.String, cDocLinDescripcion)
                Db.AddInParameter(Cmd, "nCarCodigo", System.Data.DbType.String, nCarCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Ins_ForoCampus(ByVal nCurGruCodigo As Long, ByVal tForoDesc As String, ByVal cPerCodigo As String, ByVal cForoPadre As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Ins_ForoCampus")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Db.AddInParameter(Cmd, "tForoDesc", System.Data.DbType.String, tForoDesc)
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "cForoPadre", System.Data.DbType.String, cForoPadre)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Ins_Cam_Help(ByVal nCurGruCodigo As Long, ByVal nGruCodigo As Long, ByVal nTipo As Integer, ByVal cHelDescripcion As String, ByVal cPerCodigo As String, ByVal nNodo As Integer) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Ins_Cam_Help")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Db.AddInParameter(Cmd, "nGruCodigo", System.Data.DbType.Int32, nGruCodigo)
                Db.AddInParameter(Cmd, "nTipo", System.Data.DbType.Int32, nTipo)
                Db.AddInParameter(Cmd, "cHelDescripcion", System.Data.DbType.String, cHelDescripcion)
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nNodo", System.Data.DbType.Int32, nNodo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Ins_Cam_HelDetalle(ByVal nCurGruCodigo As Long, ByVal nGruCodigo As Long, ByVal Ide As Long, ByVal nTipo As Integer, ByVal cHelDescripcion As String, ByVal cPerCodigo As String, ByVal nNodo As Integer) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Ins_Cam_HelDetalle")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Db.AddInParameter(Cmd, "nGruCodigo", System.Data.DbType.Int32, nGruCodigo)
                Db.AddInParameter(Cmd, "Ide", System.Data.DbType.Int32, Ide)
                Db.AddInParameter(Cmd, "nTipo", System.Data.DbType.Int32, nTipo)
                Db.AddInParameter(Cmd, "cHelDescripcion", System.Data.DbType.String, cHelDescripcion)
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nNodo", System.Data.DbType.Int32, nNodo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Ins_Anuncio(ByVal cDocObserv As String, ByVal cPerCodigo As String, ByVal nGruCodigo As Long, ByVal tDocConContenido As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Ins_Anuncio")
                Db.AddInParameter(Cmd, "cDocObserv", System.Data.DbType.String, cDocObserv)
                Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nGruCodigo)
                Db.AddInParameter(Cmd, "tDocConContenido", System.Data.DbType.String, tDocConContenido)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

#End Region

#Region "Update"

        Public Function Upd_Estado_Recurso_By_cDocCodigo_nDocLinNum(ByVal cDocCodigo As String, ByVal nDocLinNum As Integer, ByVal nEstado As Integer) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Upd_Estado_Recurso_By_cDocCodigo_nDocLinNum")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Db.AddInParameter(Cmd, "nDocLinNum", System.Data.DbType.Int32, nDocLinNum)
                Db.AddInParameter(Cmd, "nEstado", System.Data.DbType.Int32, nEstado)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Upd_ForoCampus_By_nCurGruCodigo_nForo(ByVal nCurGruCodigo As Long, ByVal nForo As Integer, ByVal estado As Integer) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Upd_ForoCampus_By_nCurGruCodigo_nForo")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Db.AddInParameter(Cmd, "nForo", System.Data.DbType.Int32, nForo)
                Db.AddInParameter(Cmd, "nEstado", System.Data.DbType.Int32, estado)

                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

#End Region

#Region "Delete"
        Public Function Del_Recurso_By_cDocCodigo_nItem(ByVal cDocCodigo As String, ByVal nDocLinNum As Integer) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Del_Recurso_By_cDocCodigo_nItem")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Db.AddInParameter(Cmd, "nItem", System.Data.DbType.Int32, nDocLinNum)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Del_ForoCampus_By_nCurGruCodigo_nForo(ByVal nCurGruCodigo As Long, ByVal nForo As Integer) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Del_ForoCampus_By_nCurGruCodigo_nForo")
                Db.AddInParameter(Cmd, "nCurGruCodigo", System.Data.DbType.Int32, nCurGruCodigo)
                Db.AddInParameter(Cmd, "nForo", System.Data.DbType.Int32, nForo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

        Public Function Del_Anuncio(ByVal cDocCodigo As String) As DbCommand
            Try

                Dim Cmd As DbCommand = Db.GetStoredProcCommand("usp_Campus_Del_Anuncio")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

#End Region

#Region "Evaluaciones"


        Public Function Get_Evaluacion_TipoNota_By_Registro_And_SubPeriodo(ByVal cDocCodigo As String, _
                                                                           ByVal nSubPrdCodigo As Long) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_Evaluacion_TipoNota_Get_By_Registro_SubPeriodo")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Db.AddInParameter(Cmd, "nSubPrdCodigo", System.Data.DbType.Int32, nSubPrdCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

#End Region

#End Region

    End Class

End Namespace
